package org.gw.building.repositories;

import org.gw.building.model.Bm;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface ZzjsRepository extends CrudRepository<Bm, String>{
	
	/*@Query(nativeQuery=true,value="select new org.gw.building.model.Tzbfb(t.cou1,t.cou2,t.cou3,t.cou4,t.cou5,t.cou6,t.cou7) form(select (select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='1' and b.jlny=?1) as cou1, "
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='1' and b.jlny=?1-1) as cou2,"
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='1' and b.jlny=?1-2) as cou3,"
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='1' and b.jlny=?1-3) as cou4,"
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='1' and b.jlny=?1-4) as cou5,"
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='2') as cou6,"
			+"(select count(1) from T_XTGL_BM b where b.cc='4' and b.lb='3') as cou7) t")*/
	@Query("select count(1) from Bm b where b.cc.dm='4' and b.lb.dm='1' and b.jlny=?1")
	int queryTzbfb(int year);
	
	@Query("select count(1) from Bm b where b.cc.dm='4' and b.lb.dm=?1")
	int queryTzbfb1(String lb);
	
	@Query("select count(1) from Tyxxb b where b.tgb=?1 and b.lx.dm=?2")
	int queryTgbfb(boolean tgb,String lx);
	
	@Query("select count(1) from Tyxxb b where b.whcd.dm=?1")
	int queryTgbxlfb(String xl);
	
	@Query(nativeQuery=true,value="select count(1) from(" + 
		"select case when DATE_FORMAT(now(),'%m%d') >= DATE_FORMAT(csrq,'%m%d') then DATE_FORMAT(now(),'%Y') - DATE_FORMAT(csrq,'%Y') else DATE_FORMAT(now(),'%Y') - DATE_FORMAT(csrq,'%Y') -1 end nl " + 
		"from t_tqtj_tyxxb where csrq is not null " + 
		") t where t.nl>=?1 and t.nl<=?2")
	int queryTgbnlfb(int start,int end);
	
	@Query("select count(1) from Tyxxb b where b.zzmm.dm in('01','02','03') and b.lx.dm='1'")
	int queryTydy();
	
	@Query("select count(1) from Tyxxb b where b.zzmm.dm='03' and b.lx.dm='1'")
	int queryTy();
	
	@Query(nativeQuery=true,value="select count(case when DATE_FORMAT(now(),'%Y') = DATE_FORMAT(rtrq,'%Y') then 1 else 0 end) from t_tqtj_tyxxb where rtrq is not null")
	int queryTyNew();
	
	@Query(nativeQuery=true,value="select count(1) from(" + 
		"select case when DATE_FORMAT(now(),'%m%d') >= DATE_FORMAT(csrq,'%m%d') then DATE_FORMAT(now(),'%Y') - DATE_FORMAT(csrq,'%Y') else DATE_FORMAT(now(),'%Y') - DATE_FORMAT(csrq,'%Y') -1 end nl " + 
		"from t_tqtj_xs where csrq is not null " + 
		") t where t.nl<=28")
	int queryTyqn();
	
	@Query(nativeQuery=true,value="select count(1) from T_TQTJ_TYXXB b where DATE_FORMAT(now(),'%Y') = DATE_FORMAT(b.sqrdrq,'%Y')")
	int queryTysqdy();
	
	@Query(nativeQuery=true,value="select count(1) from T_TQTJ_TYXXB b where DATE_FORMAT(now(),'%Y') = DATE_FORMAT(b.tntyrq,'%Y')")
	int queryTytys();
	
	@Query(nativeQuery=true,value="select count(1) from T_TQTJ_TYXXB b where DATE_FORMAT(now(),'%Y') = DATE_FORMAT(b.fzwybdyrq,'%Y')")
	int queryTyrds();
	
	@Query(nativeQuery=true,value="select count(1) from T_TQTJ_TYXXB b where DATE_FORMAT(now(),'%Y') = DATE_FORMAT(b.sqrdrq,'%Y') and b.tntyrq is not null")
	int queryTytyrds();
}